import scrapy
from faker import Faker
import random
import time
import json
import math
import pymysql
import proxyandua
# import socket
# import socks
from fake_useragent import UserAgent
import uuid

import requests

class Ut:
	# ua = Faker(locale="zh_CN").user_agent()
	# ua = random.choice(proxyandua.ua)
	ua = UserAgent().random
	header = {
		"User-Agent":ua
		# "User-Agent":'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:82.0) Gecko/20100101 Firefox/82.0'
	}

	proxy = "http://"+random.choice(proxyandua.pox)
	proxies = {"http":proxy}

	db_conn = None
	cursor = None

# 连接数据库
def connect_db():
	try :
		db = 'test'
		host = '127.0.0.1'
		port = 3306
		user =  'root'
		passwd = '1234'

		# 建立数据库连接
		Ut.db_conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset='utf8mb4')
		# 获取游标
		Ut.cursor = Ut.db_conn.cursor()
		print("mysql connet successful")
	except:
		print("pymysql connect have something wrong")

# 关闭数据库连接
def close_db():
	Ut.cursor.close()
	Ut.db_conn.close()
	print("pymysql connect close successful")

def start():
	connect_db()

	# sql = "select shopId,allCommentNum from pds WHERE (commentTags IS NULL and comments IS NULL) "
	sql = "select shopId,allCommentNum from pds WHERE (comments IS NULL AND allCommentNum !=0) "
	Ut.cursor.execute(sql)
	results = Ut.cursor.fetchall()

	uid = str(uuid.uuid4())

	for shopId,allCommentNum in results:
		all_page = int(math.ceil(float(allCommentNum)/10))
		if all_page == 0 :
			continue
		comments_list = []
		print("start scrapy shopId:"+shopId)
		for page in range(all_page):

			url = "https://www.meituan.com/meishi/api/poi/getMerchantComment?uuid="+uid+"&platform=1&partner=126&originUrl=https://www.meituan.com/meishi/"+str(shopId)+"/&riskLevel=1&optimusCode=10&id="+str(shopId)+"&userId=&offset="+str(page*10)+"&pageSize=10&sortType=1"
			# yield scrapy.Request(url,headers=Ut.header,callback=Ut.parse,dont_filter=True,meta={'shopId':shopId,'page':page,'all_page':all_page})
			# yield scrapy.Request(url,callback=Ut.parse,dont_filter=True,meta={'proxy':"http://localhost:9050",'shopId':shopId,'page':page,'all_page':all_page})
			# yield scrapy.Request(url,callback=Ut.parse,dont_filter=True,meta={'shopId':shopId,'page':page,'all_page':all_page})
			time.sleep(random.uniform(0,1))
			while True:
				try:
					data = json.loads(requests.get(url,headers=Ut.header,proxies=Ut.proxies).text)['data']
					# data = json.loads(requests.get(url).text)['data']
					print(str(shopId)+":"+str(page)+"/"+str(all_page))
					print("请求成功")
					break
				except:
					print("请求失败，更换IP和UA")
					uid = str(uuid.uuid4())
					insert_data_comments(shopId,json.dumps(comments_list,ensure_ascii=False))
					# socks.set_default_proxy(socks.SOCKS5, "127.0.0.1", 9050)
					# socket.socket = socks.socksocket
					# print("更换torIP")

			if page == 0:
				# print("start get tag")
				all_tags = data['tags']
				if all_tags is not None:
					tags = []
					for t in all_tags:
						tags.append({'tag':t['tag'],'count':t['count']})
					insert_data_tags(shopId,tags)

			comments = data['comments']
			if comments is not None:
				# print("star get comments")
				none_flag = False
				for comment in comments:
					if comment['comment'] == "":
						print("抛弃空评论，结束本餐厅，开始下一个餐厅")
						none_flag = True
						break
					comm = comment['comment']
					commentTime = comment['commentTime']
					star = comment['star']
					reviewId = comment['reviewId']
					comments_list.append(reviewId)
					dic = {'reviewId':reviewId,'comment':comm,'commentTime':commentTime,'star':star}
					insert_data_review(dic)
				if none_flag == True or page == all_page-1 :
					insert_data_comments(shopId,json.dumps(comments_list,ensure_ascii=False))
					print("scrapy page end of "+str(shopId))
					break

			# if ((comments is None) and (page == all_page)) or ((comments is not None) and (page==all_page-1)) :

					# get_data(shopId,data,page,all_page)

	close_db()

def get_data(shopId,data,page,all_page):
	if page == 0:
		all_tags = data['tags']
		if all_tags is not None:
			tags = []
			for t in all_tags:
				tags.append({'tag':t['tag'],'count':t['count']})
			insert_data_tags(shopId,tags)

	comments = data['comments']
	if comments is not None:
		for comment in comments:
			if comment['comment'] is None:
				continue
			comm = comment['comment']
			commentTime = comment['commentTime']
			star = comment['star']
			reviewId = comment['reviewId']
			comments_list.append(reviewId)
			dic = {'reviewId':reviewId,'comment':comm,'commentTime':commentTime,'star':star}
			insert_data_review(dic)

	# if ((comments is None) and (page == all_page)) or ((comments is not None) and (page==all_page-1)) :
	if page == (all_page-1):
		insert_data_comments(shopId,json.dumps(comments_list,ensure_ascii=False))
		print("scrapy page end of "+str(shopId))

def insert_data_tags(shopId,tags):
	try:
		Ut.cursor.execute("update pds set commentTags=%s where shopId="+str(shopId),json.dumps(tags,ensure_ascii=False))
		# Ut.cursor.execute("update pds set comments=%s where shopId="+str(item['shopId']),item['comments'])
		Ut.db_conn.commit()
		print("tags_data insert successful")
	except:
		print("tags_data insert failure")
		Ut.db_conn.rollback()		# 回滚数据

def insert_data_review(dic):

	reviewId = dic['reviewId']
	comment = dic['comment']
	commentTime = dic['commentTime']
	star = dic['star']

	data = (int(reviewId),str(comment),int(commentTime),int(star))

	sql = "insert into pds_review values(%s,%s,%s,%s)"

	# print(data)
	try:
		Ut.cursor.execute(sql,data)
		Ut.db_conn.commit()
		print("review_data insert successful")
	except:
		print("review_data insert failure")
		Ut.db_conn.rollback()		# 回滚数据

def insert_data_comments(shopId,comments_list):
	try:
		# Ut.cursor.execute("update pds set commentTags=%s where shopId="+str(item['shopId']),item['commentTags'])
		Ut.cursor.execute("update pds set comments=%s where shopId="+str(shopId),comments_list)
		Ut.db_conn.commit()
		print("comments_data insert successful")
	except:
		print("comments_data insert failure")
		Ut.db_conn.rollback()		# 回滚数据

start()